﻿CREATE PROCEDURE [dbo].[Hogar_Integrante_ObtenerTodos]   
AS 
BEGIN

/*****************************************************************************************************************************************/
-- Author: Carlos Mendoza
-- Purpose:   obtener todos los agentes con sus datos
-- Returns:    ConsultaIntegrante
/*****************************************************************************************************************************************/ 
	
	UPDATE [dbo].[Integrante]
	SET [Activo] = 0, [FechaBaja] = NULL --GETDATE()
	WHERE [Activo] = 1 AND YEAR([FechaInscripcion]) < YEAR(GETDATE())

	SELECT  i.[IntegranteId],
			[Dni],
			[Nombre],
			[Apellido],
			CASE WHEN [Activo] = 1 THEN 1 ELSE 0 END AS [Activo],
			i.[TipoDocumentoId],
			[FechaNacimiento],
			[Domicilio],
			[Telefono],
			i.[EstadoCivilId],
			i.[NivelEstudioId],
			CASE WHEN [NivelEstudioCompleto] = 1 THEN 1 ELSE 0 END AS [NivelEstudioCompleto],
			i.[PropiedadViviendaId],
			i.[BeneficioPrevisionalId],
			[Monto],
			i.[ObraSocialId],
			[NroAfiliado],
			i.[ServicioEmergenciaId],
			[CuotaCooperadora],
			[FechaBaja],
			ISNULL([ObservacionBaja],'') AS [ObservacionBaja],
			[FechaInscripcion],
			ISNULL([Entrevista],'') AS [Entrevista],
			CASE ISNULL((SELECT TOP 1 [ActividadId] FROM [AsisteComedorViandaDias] cv WITH (NOLOCK) WHERE cv.IntegranteId = i.IntegranteId AND cv.ActividadId = 2),0) WHEN 0 THEN 0 ELSE 1 END AS AsisteVianda,
			CASE ISNULL((SELECT TOP 1 [ActividadId] FROM [AsisteComedorViandaDias] cv WITH (NOLOCK) WHERE cv.IntegranteId = i.IntegranteId AND cv.ActividadId = 1),0) WHEN 0 THEN 0 ELSE 1 END AS AsisteComedor,
			bp.[Descripcion] AS [BeneficioPrevisional],
			ec.[Descripcion] AS [EstadoCivil],
			ne.[Descripcion] AS [NivelEstudio],
			ISNULL(os.[Descripcion],'') AS [ObraSocial],
			pv.[Descripcion] AS [PropiedadVivienda],
			ISNULL(se.[Descripcion], '') AS [ServicioEmergencia],
			td.[Descripcion] AS TipoDocumento			
	FROM	[dbo].[Integrante] i WITH (NOLOCK)
	INNER JOIN [dbo].[TipoDocumento] td WITH (NOLOCK) ON td.TipoDocumentoId = i.TipoDocumentoId
	INNER JOIN [dbo].[EstadoCivil] ec WITH (NOLOCK) ON ec.EstadoCivilId = i.EstadoCivilId 
	INNER JOIN [dbo].[PropiedadVivienda] pv WITH (NOLOCK) ON pv.PropiedadViviendaId = i.PropiedadViviendaId
	INNER JOIN [dbo].[BeneficioPrevisional] bp WITH (NOLOCK) ON bp.BeneficioPrevisionalId = i.BeneficioPrevisionalId
	LEFT JOIN [dbo].[ObraSocial] os WITH (NOLOCK) ON os.ObraSocialId = i.ObraSocialId
	INNER JOIN [dbo].[NivelEstudio] ne WITH (NOLOCK) ON ne.NivelEstudioId = i.NivelEstudioId
	LEFT JOIN [dbo].[ServicioEmergencia] se WITH (NOLOCK) ON se.ServicioEmergenciaId = i.ServicioEmergenciaId
	ORDER BY [Apellido],[Nombre]

	SELECT [IntegranteId],
		   [ActividadId]
	FROM [dbo].[Inscripcion] WITH (NOLOCK)
	WHERE [Activa] = 1 AND [ActividadId] > 2
	ORDER BY [IntegranteId]
END